Back to Main Menu

Using Advanced Search to Bulk Export

Sample Script

The following Python script illustrates how to execute a search that spans multiple pages, and save the results to a csv file.

 

  1. """
  2.     Example script to run search profile (Assetic.SearchGetAPI.py)
  3.     Search is paginated so set page size.  On a per page basic the search
  4.     results omit columns where all records are null for that column. This is
  5.     catered for by building a list of columns returned by all pages
  6. """
  7. import assetic
  8. import csv
  9. import functools
  10. import sys
  11.   
  12. # Assetic SDK instance
  13. asseticsdk = assetic.AsseticSDK("c:/users/you/assetic.ini",None,"Info")
  14.   
  15. # Search API
  16. sapi = assetic.SearchApi()
  17.   
  18. # Define search parameters
  19. searchguid =  "f9149c1b-0a43-e611-945f-06edd62954d7"
  20. numpagesize =  50
  21. # Return only those records where Asset Class = "Transport"
  22. searchfilter =  "ComplexAssetClass=Transport"
  23.   
  24. # set search criteria as keyword args
  25. kw =  {"request_params_id":searchguid,
  26.   "request_params_page":1,
  27.   "request_params_sorts":"ComplexAssetName-desc",
  28.   "request_params_page_size":numpagesize,
  29.   "request_params_filters":searchfilter}
  30.   
  31. # Get first page of results
  32. try:
  33.     sg = sapi.search_get(**kw)
  34. except assetic.rest.ApiException  as e:
  35.     asseticsdk.logger.error("Status {0}, Reason: {1} {2}".format(
  36.         e.status, e.reason, e.body))
  37. # Log the number of records
  38. totalresults=sg.get("TotalResults")
  39. numpages = sg.get("TotalPages")
  40. asseticsdk.logger.info("Total Results: {0}, Total Pages: {1}".format(
  41.     totalresults,numpages))
  42.   
  43. # get data from nested output
  44. resourcelist = sg.get("ResourceList")
  45. resource = resourcelist[0]
  46. data = resource.get("Data")
  47.   
  48. # Copy as "alldata" because we will be appending to this
  49. alldata = data
  50.   
  51. # Get a list of columns
  52. columns = map(  lambda x: x.keys(), data )
  53. if sys.version_info <  (3,0):
  54.     columns = reduce(  lambda x,y: x+y, columns )
  55. else:
  56.     columns = functools.reduce(  lambda x,y: x|y, columns )
  57.   
  58. # Write list of columns to the "all" list as this may grow
  59. allcolumns = columns
  60.   
  61. # Now loop through remaining pages
  62. if numpages >  1:
  63.   for pagenum in range(2,int(numpages)  +  1):
  64.   # set page number to get
  65.         kw["request_params_page"]=pagenum
  66.         asseticsdk.logger.info("Page: {0}".format(kw["request_params_page"]))
  67.   
  68.   # Now get results for this page
  69.   try:
  70.             sg = sapi.search_get(**kw)
  71.   except assetic.rest.ApiException  as e:
  72.             asseticsdk.logger.error("Status {0}, Reason: {1} {2}".format(
  73.                 e.status, e.reason, e.body))
  74.   # get actual data from nested output
  75.         resourcelist = sg.get("ResourceList")
  76.         resource = resourcelist[0]
  77.         data = resource.get("Data")
  78.   
  79.   # Get column list for this page - there may be new columns
  80.         columns = map(lambda x: x.keys(), data)
  81.   if sys.version_info <  (3,  0):
  82.             columns = reduce(lambda x,y: x+y, columns)
  83.   # Add new column list to "allcolumns", will get unique list later
  84.             allcolumns = allcolumns + columns
  85.   # append new data to "alldata"
  86.             alldata = alldata + data
  87.   else:
  88.             columns = functools.reduce(lambda x,y: x|y, columns)
  89.   # merge column list sets
  90.             allcolumns = allcolumns | columns
  91.   # append new data to "alldata"
  92.             alldata.extend(data)
  93.   
  94.   if pagenum >  10:
  95.   # catchall escape
  96.   break
  97.   
  98.   
  99. if sys.version_info <  (3,  0):
  100.   # get unique list of columns
  101.     columns = list(set(allcolumns))
  102. else:
  103.     columns = allcolumns
  104.   
  105. # create csv
  106. if sys.version_info <  (3,0):
  107.   with open(  "c:/temp/road_dump.csv",  "wb"  )  as out_file:
  108.         csv_w = csv.writer(out_file)
  109.         csv_w.writerow(columns)
  110.   
  111.   for i_r in alldata:
  112.   # map data to column list by key to avoid potential issues
  113.   # with column order
  114.             csv_w.writerow( map(  lambda x: i_r.get( x,  ""), columns))
  115. else:
  116.   with open(  "c:/temp/road_dump3.csv",  "w", newline="")  as out_file:
  117.         csv_w = csv.writer(out_file)
  118.         csv_w.writerow(columns)
  119.   
  120.   for i_r in alldata:
  121.   # map data to column list by key to avoid potential issues
  122.   # with column order
  123.             csv_w.writerow(map(lambda x: i_r.get( x,  ""), columns))

How it works

The search parameters are defined. The searchguid is the unique ID of the search profile that will be exported (refer to this article).  The parameters include a filter by Asset Class = "Transport" to restrict the search results to only those records where the Asset Class is "Transport".

# Define search parameters searchguid =  "f9149c1b-0a43-e611-945f-06edd62954d7" numpagesize =  50  # Return only those records where Asset Class = "Transport" searchfilter =  "ComplexAssetClass=Transport"

The parameter "request_params_page" is set to '1' so that the first page of results are returned. The number of records is set by "request_params_page_size". The records are returned ordered by Asset Id in ascending order, set by "request_params_sorts, and filtered by request_params_filters"

# set search criteria as keyword args kw =  {"request_params_id":searchguid,  "request_params_page":1,  "request_params_sorts":"ComplexAssetName-desc",  "request_params_page_size":numpagesize,  "request_params_filters":searchfilter}

 

The search is executed and the array of data obtained

# Get first page of results sg = sapi.search_get(**kw)  # get actual data from nested output resourcelist = sg.get('ResourceList') resource = resourcelist[0] data = resource.get('Data')

Each page in subsequent requests may return a different number of fields. The following code block manages this by building a list of columns and holding an array of data (field name and value pairs).

# Copy as alldata becuase we will be appending to this alldata = data  # Get a list of columns columns = map(  lambda x: x.keys(), data ) columns = functools.reduce(  lambda x,y: x|y, columns )  # Write list of columns to the 'all' list as this may grow allcolumns = columns

 

Subsequent pages are then requested as a loop using the total number of records and page size to determine the number of loops

  for pagenum in range(2,int(numpages)  +  1):  # set page number to get         kw['request_params_page']=pagenum          asseticsdk.logger.('Page: {0}'.format(kw['request_params_page']))  # Now get results for this page  try:             sg = sapi.search_get(**kw)  except assetic.rest.ApiException  as e:             asseticsdk.logger.error("Status {0}, Reason: {1} {2}".format(                 e.status, e.reason, e.body))

 

The data array for each page is added to the "alldata" array and the column list "columns" updated to include the columns in the page.

# get actual data from nested output         resourcelist = sg.get('ResourceList')         resource = resourcelist[0]         data = resource.get('Data')  # append new data to 'alldata'         alldata = alldata + data         # Get column list for this page - there may be new columns         columns = map(lambda x: x.keys(), data)         columns = functools.reduce(lambda x,y: x|y, columns)  # merge column list sets
        allcolumns = allcolumns | columns

Using the unique list of columns and the key/value pairs in the data array the csv file is created. This method also ensures that the data is written to the correct fields, since if there are variable field numbers in each page then the field order would be incorrect if the data were simply written to file page by page.

# create csv  with open('c:/temp/road_dump.csv',  'w', newline='')  as out_file:     csv_w = csv.writer(out_file)     csv_w.writerow(columns)  for i_r in alldata:  # map data to column list by key to avoid potential issues with column order         csv_w.writerow(map(lambda x: i_r.get(x,  ""), columns))